﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Database;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class AB45MATERIALData : BaseFunction
    {
        #region -------------function -------------------------
        public int ThreadCount;
        public INTEXDataSet setUI;
        string ABPOS = "";
        public AB45MATERIALData()
            : base()
        {

        }
        public AB45MATERIALData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public AB45MATERIALData(OracleConnection _OracleDB, IntexReptServBLClient _proxy)
            : base(_OracleDB, _proxy)
        {

        }
        public AB45MATERIALData(OracleConnection _OracleDB, IntexReptServBLClient _proxy,INTEXDataSet _setUI)
            : base(_OracleDB, _proxy)
        {
            this.setUI = _setUI;
        }
        ~AB45MATERIALData()
        {
            if (OracleDB != null)
            {
                OracleDB.Close();
            }
        }
        public DataSet LoadABPOS(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {
                string ABPAUFKRS = dwABPOS["ABPAUFKRS"].ToString();
                string ABPAUFLFD = dwABPOS["ABPAUFLFD"].ToString();
                string ABPAUFPOS = dwABPOS["ABPAUFPOS"].ToString();
                string ARTICLE = dwABPOS["ARTICLE"].ToString();
                //string ABPLIEFNR = dwABPOS["LSNAME1"].ToString().Trim();
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT TRIM(C.LSNAME1) AS SFACTORY,A.ABPSTARTDAT, "
                    + " A.ABPAUFKRS,A.ABPAUFLFD,A.ABPAUFPOS,'' AS ORDERNO,trim(K.ABKKDAUFTNR) as ABKKDAUFTNR, "
                    + " TRIM(A.ABPART)||'-'||TRIM(A.ABPFARBE)||'-'||TRIM(A.ABPVAR) AS ARTICLE, "
                    + " E.BSFBEZ,A.ABPSOLLMENGE1*0.01 AS ORDERMTR,'' AS WFACTORY,A.ABPERLDAT,A.ABPSOLLMENGE8*0.01 AS BQTY, "
                    + " A.ABPEALFM*0.01 AS ACTMTR,'' AS BATCHNO,D.GAART,D.GAVAR,TRIM(F.GSGARNBEZ) AS DES, "
                    + " D.GAFARBE,TRIM(G.GSCBEZ) AS COLORDES,D.GASPUL AS GQTY,D.GAKG5*0.01 AS ALLOKG, "
                    + " 0 AS SOLLKG,0 AS ACTKG,'' AS SUPPLIER,A.ABPENDDAT, "
                    + " (CASE WHEN A.ABPERLDAT >0 THEN  "
                    + " (CASE WHEN A.ABPENDDAT >0 THEN TO_DATE(A.ABPERLDAT,'YYYYMMDD')-TO_DATE(A.ABPENDDAT,'YYYYMMDD')  "
                    + " ELSE A.ABPERLDAT END) ELSE -A.ABPENDDAT END ) AS DIFF "
                    + " FROM ABPOS A,ABFOLGE B,LIEFDAT C,GAAUF D,BSFARB E,GSDAT F,GSCOLOR G,ABKOPF K "
                    + " WHERE A.ABPMANDANT = B.ABFMANDANT AND A.ABPAUFKRS = B.ABFAUFKRS "
                    + " AND A.ABPAUFLFD = B.ABFAUFLFD AND A.ABPAUFPOS = B.ABFAUFPOS "
                    + " AND A.ABPMANDANT = K.ABKMANDANT AND A.ABPAUFKRS = K.ABKAUFKRS AND A.ABPAUFLFD = K.ABKAUFLFD "
                    + " AND B.ABFMANDANT = C.LSMANDANT AND B.ABFLIEFNR = C.LSLIEFNR "
                    + " AND A.ABPMANDANT = D.GAMANDANT AND A.ABPAUFKRS = D.GAAUFKRS "
                    + " AND A.ABPAUFLFD = D.GAAUFLFD AND A.ABPAUFPOS = D.GAAUFPOS "
                    + " AND A.ABPMANDANT = E.BSFMANDANT AND A.ABPART = E.BSFKART "
                    + " AND TRIM(A.ABPVAR) = E.BSFKVAR AND A.ABPFARBE = E.BSFKFARBE "
                    + " AND D.GAMANDANT = F.GSMANDANT(+) AND D.GAART = F.GSART(+) "
                    + " AND D.GAVAR = F.GSVAR(+) AND D.GAMANDANT = G.GSCMANDANT(+) "
                    + " AND D.GAART = G.GSCART(+) AND D.GAVAR = G.GSCVAR(+) "
                    + " AND D.GAFARBE = G.GSCFARBE(+) AND TRIM(B.ABFAGANG) = 'SZ' AND D.GASA = '723' "
                    + " AND A.ABPAUFKRS LIKE '%" + ABPAUFKRS + "%' AND A.ABPAUFLFD LIKE '%" + ABPAUFLFD
                    + "%' AND A.ABPAUFPOS LIKE '%" + ABPAUFPOS + "%' AND A.ABPART LIKE '%" + ARTICLE + "%' "
                    + " AND (A.ABPERLKZ = :ABPERLKZ  OR :ABPERLKZ = 'J') "
                    //+ " AND A.ABPAUFKRS = 'SB' AND A.ABPAUFLFD = '001309' AND A.ABPAUFPOS = '010' "
                    + " AND A.ABPSTARTDAT >= :ABPSTARTDAT AND A.ABPSTARTDAT <= :ABPENDDAT "
                    + " AND A.ABPERLDAT >= :SDAT AND A.ABPERLDAT <= :EDAT "
                    ;
                or1.Parameters.Add("ABPERLKZ", OracleType.NVarChar).Value = dwABPOS["ABPERLKZ"].ToString();
                or1.Parameters.Add("ABPSTARTDAT", OracleType.NVarChar).Value = dwABPOS["STARTDATE"].ToString();
                or1.Parameters.Add("ABPENDDAT", OracleType.NVarChar).Value = dwABPOS["ENDDATE"].ToString();
                or1.Parameters.Add("SDAT", OracleType.NVarChar).Value = dwABPOS["SDAT"].ToString();
                or1.Parameters.Add("EDAT", OracleType.NVarChar).Value = dwABPOS["EDAT"].ToString();
                setLocal = Connect2Proxy(or1);

               if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "YABPOS";
                    
                    byte[] b;
                    string value;
                    string column = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        column = "SFACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        column = "BSFBEZ";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        column = "DES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        column = "COLORDES";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                         dwTemp["ORDERNO"] = dwTemp["ABPAUFKRS"].ToString().Trim()
                            + "-" + System.Convert.ToInt16(dwTemp["ABPAUFLFD"].ToString().Trim()) 
                            + "-" + System.Convert.ToInt16(dwTemp["ABPAUFPOS"].ToString().Trim());
                        GetGAAUF(dwTemp);
                        GetGAAUF1(dwTemp);
                        GetBABST(dwTemp);
                        GetGFABRICEINFOByDI(dwTemp);
                }
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }

        private DataSet GetGFABRICEINFOByDI(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {

                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT LSNAME1 AS GPRODUCER,ABPART,ABPVAR,ABPFARBE  FROM ABAUFM M,ABPOS P,LIEFDAT L,ABFOLGE F "
                    + " WHERE M.ABAMANDANT = P.ABPMANDANT AND M.ABAVKAUFKRS = P.ABPAUFKRS "
                    + " AND M.ABAVKAUFLFD = P.ABPAUFLFD AND M.ABAVKAUFPOS = P.ABPAUFPOS "
                    + " AND P.ABPMANDANT = F.ABFMANDANT AND P.ABPAUFKRS = F.ABFAUFKRS AND P.ABPAUFLFD = F.ABFAUFLFD "
                    + " AND P.ABPAUFPOS = F.ABFAUFPOS AND F.ABFMANDANT = L.LSMANDANT "
                    + " AND F.ABFLIEFNR = L.LSLIEFNR AND P.ABPAUFART in ('DI','PR') AND M.ABAMANDANT = '00' "
                    + " AND M.ABAAUFKRS=:ABPAUFKRS AND M.ABAAUFLFD=:ABPAUFLFD AND M.ABAAUFPOS=:ABPAUFPOS";
                or1.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                or1.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                or1.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setLocal = Connect2Proxy(or1);

                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "ABFOLGE";
                    byte[] b;
                    string value;
                    string sTemp = "", GPRODUCER = "", column,article = "",art = "";
                    double GMETER = 0;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {

                        column = "GPRODUCER";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        sTemp = value + ";";
                        if (!GPRODUCER.Contains(sTemp)
                           && sTemp != ";")
                        {
                            GPRODUCER += sTemp;
                        }
                        art = dwTemp["ABPART"].ToString().Trim()
                         + "-" + dwTemp["ABPFARBE"].ToString().Trim() + "-" + dwTemp["ABPVAR"].ToString().Trim() + ";";
                        if (!article.Contains(art)
                           && art != ";")
                        {
                            article  += art;
                        }
                        
                    }
                    GPRODUCER += "***";
                    GPRODUCER = GPRODUCER.Replace(";***", "");
                    GPRODUCER = GPRODUCER.Replace("***", "");
                    GPRODUCER = GPRODUCER.Replace(";", Environment.NewLine);
                    article += "***";
                    article = article.Replace(";***", "");
                    article = article.Replace("***", "");
                    article = article.Replace(";", Environment.NewLine);
                    dwABPOS["WFACTORY"] = GPRODUCER;
                    dwABPOS["ARTICLE"] = article;
                    Tracer.Verbose(ABPOS + " 织造厂：", GPRODUCER);
                    set4Return.Merge(setLocal);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return set4Return;
        }
        public DataSet GetBABST(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = " SELECT TRIM(A.BMBEM) AS BATCHNO,SUM(A.BMISTLG*0.1) AS ACTMTR,  "
                    + " SUM(CASE WHEN A.BMISTLG = 0 THEN 0 ELSE 1 END) AS ACTQTY FROM BABST A  "
                    + " WHERE A.BMAUFKRS = :ABPAUFKRS  AND A.BMAUFLFD = :ABPAUFLFD "
                    + " AND A.BMAUFPOS = :ABPAUFPOS AND A.BMMANDANT = '00' "
                    + " GROUP BY TRIM(A.BMBEM) "
                   
               ;

                or1.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                or1.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                or1.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                setLocal = Connect2Proxy(or1);

                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "BABST";

                    string BATCHNO = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                         BATCHNO += dwTemp["BATCHNO"].ToString().Trim()+"缸"
                             + System.Convert.ToDouble(dwTemp["ACTMTR"].ToString())+ "米"
                             + System.Convert.ToDouble(dwTemp["ACTQTY"].ToString()) + "个轴;"; 
                                             
                    }
                    BATCHNO += "***";
                    BATCHNO = BATCHNO.Replace(";***", "");
                    BATCHNO = BATCHNO.Replace("***", "");
                    BATCHNO = BATCHNO.Replace(";", Environment.NewLine);
                    dwABPOS["BATCHNO"] = BATCHNO;
                }
                set4Return.Merge(setLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return set4Return;
        }
      
        public DataSet GetGAAUF(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = " SELECT GABKG*0.001 AS GABKG,C.LSNAME1 FROM GABEW A,GPDAT B,LIEFDAT C "
                    + " WHERE A.GABMANDANT = B.GPMANDANT AND A.GABPARTIE = B.GPPARTIE "
                    + " AND A.GABPARTIEKZ = B.GPPARTIEKZ AND B.GPMANDANT = C.LSMANDANT(+) "
                    + " AND B.GPLIEFNR = C.LSLIEFNR(+) AND A.GABBS = '37' "
                    + " AND A.GABAUFTYP = 'V' AND A.GABMANDANT = '00' "
                    + " AND A.GABAUFKRS = :ABPAUFKRS AND A.GABAUFLFD = :ABPAUFLFD "
                    + " AND A.GABAUFPOS = :ABPAUFPOS AND A.GABART = :ABPART "
                    + " AND A.GABVAR = :ABPVAR AND A.GABFARBE = :ABPFARBE "
                    ;

                or1.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                or1.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                or1.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                or1.Parameters.Add("ABPART", OracleType.NVarChar).Value = dwABPOS["GAART"].ToString();
                or1.Parameters.Add("ABPVAR", OracleType.NVarChar).Value = dwABPOS["GAVAR"].ToString();
                or1.Parameters.Add("ABPFARBE", OracleType.NVarChar).Value = dwABPOS["GAFARBE"].ToString();
                setLocal = Connect2Proxy(or1);
                
                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "GAAUF";

                    byte[] b;
                    string value;
                    string column = "";
                    string SUPPLIER = "";
                    Double ACTKG = 0;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        column = "LSNAME1";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[column] = value;
                        ACTKG += System.Convert.ToDouble(dwTemp["GABKG"].ToString());
                        if (!SUPPLIER.Contains(value))
                        {
                            SUPPLIER += value + ";" ;
                        }
                       
                    }
                    SUPPLIER += "***";
                    SUPPLIER = SUPPLIER.Replace(";***", "");
                    SUPPLIER = SUPPLIER.Replace("***", "");
                    SUPPLIER = SUPPLIER.Replace(";", Environment.NewLine);
                    dwABPOS["SUPPLIER"] = SUPPLIER;
                    dwABPOS["ACTKG"] = System.Math.Round(ACTKG,2);
                }
                set4Return.Merge(setLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return set4Return;
        }
        public DataSet GetGAAUF1(DataRow dwABPOS)
        {
            DataSet set4Return = new DataSet();
            DataSet setLocal = new DataSet();
            DataTable tableLocal = null;
            try
            {
                OracleCommand or1 = OracleDB.CreateCommand();
                or1.CommandText = " SELECT GABKG*0.001 AS GABKG FROM GABEW A "
                    + " WHERE A.GABAUFTYP = 'V' AND A.GABMANDANT = '00' AND A.GABBS = '37' AND TRIM(A.GABFC)  = 'BB07' "
                    + " AND A.GABAUFKRS = :ABPAUFKRS AND A.GABAUFLFD = :ABPAUFLFD "
                    + " AND A.GABAUFPOS = :ABPAUFPOS AND A.GABART = :ABPART "
                    + " AND A.GABVAR = :ABPVAR AND A.GABFARBE = :ABPFARBE "
                    ;

                or1.Parameters.Add("ABPAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABPAUFKRS"].ToString();
                or1.Parameters.Add("ABPAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABPAUFLFD"].ToString();
                or1.Parameters.Add("ABPAUFPOS", OracleType.NVarChar).Value = dwABPOS["ABPAUFPOS"].ToString();
                or1.Parameters.Add("ABPART", OracleType.NVarChar).Value = dwABPOS["GAART"].ToString();
                or1.Parameters.Add("ABPVAR", OracleType.NVarChar).Value = dwABPOS["GAVAR"].ToString();
                or1.Parameters.Add("ABPFARBE", OracleType.NVarChar).Value = dwABPOS["GAFARBE"].ToString();
                setLocal = Connect2Proxy(or1);

                if (setLocal.Tables.Count > 0)
                {
                    tableLocal = setLocal.Tables[0];
                    tableLocal.TableName = "GAAUF";

                    byte[] b;
                    string value;
                    string column = "";
                    Double SOLLKG = 0;
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        SOLLKG += System.Convert.ToDouble(dwTemp["GABKG"].ToString());
                        
                    }
                    dwABPOS["SOLLKG"] = System.Math.Round(SOLLKG, 2);
                }
                set4Return.Merge(setLocal);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex); ;
            }
            return set4Return;
        }
        #endregion
       
    }
}